﻿using System;
using System.Data;
using System.Data.SqlClient;
using bank.DTOs;
using System.Collections.Generic;
using bank.DataSets;

using System.Configuration;
namespace bank.DataAccess
{
    public class Query
    {
        SqlConnection conn;
        SqlDataAdapter dataAdapter;
        DataSet ds;
        public Query()
        {
            conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["StudentDBConfig"].ToString();  //"server=localhost;Database=SchoolDb;user id=sa;password=sa123";

            dataAdapter = new SqlDataAdapter();

            dataAdapter.SelectCommand = GetSelectCommand();
            dataAdapter.UpdateCommand = GetUpdateCommand();
            dataAdapter.DeleteCommand = GetDeleteCommand();
            dataAdapter.InsertCommand = GetInsertCommand();
        }

        #region Select, Update, delete and insert commands

        private SqlCommand GetSelectCommand()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "select * from student";
            return cmd;
        }
        private SqlCommand GetUpdateCommand()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "update student set Name=@Name, Age=@Age where RollNo=@RollNo";
            cmd.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name");
            cmd.Parameters.Add("@Age", SqlDbType.Int, 3, "Age");
            cmd.Parameters.Add("@RollNo", SqlDbType.BigInt, 4, "RollNo");
            return cmd;
        }
        private SqlCommand GetDeleteCommand()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "delete from student where RollNo=@RollNo";
            cmd.Parameters.Add("@RollNo", SqlDbType.BigInt, 4, "RollNo");
            return cmd;
        }
        private SqlCommand GetInsertCommand()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "insert into student values(@RollNo,@Name,@Age)";
            cmd.Parameters.Add("@RollNo", SqlDbType.BigInt, 4, "RollNo");
            cmd.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name");
            cmd.Parameters.Add("@Age", SqlDbType.Int, 3, "Age");
            return cmd;
        }

        #endregion

        public School GetAllStudents2()
        {
            School dsSchool = new School();
            try
            {
                conn.Open();
                dataAdapter.Fill(dsSchool.StudentDT);


                foreach (DataTable tbl in dsSchool.Tables)
                {
                    if (Convert.ToInt32(tbl.Rows[0]["clid"]) == 7)
                    {

                    }
                }

               


            }
            finally
            {
                conn.Close();
            }
            return dsSchool;
        }

        public bool UpdateDataSet(School dsSchool)
        {
            try
            {
                conn.Open();
                dataAdapter.Update(dsSchool.StudentDT);
            }
            catch (Exception exp)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
            return true;
        }


        //public bool UpdateDataSet(School dsSchool)
        //{
        //    cmd = new SqlCommand();
        //    cmd.Connection = conn;
        //    cmd.CommandText = "insert into student values(@RollNo,@Name,@Age)";
        //    cmd.Parameters.Add("@RollNo", SqlDbType.BigInt, 4, "RollNo");
        //    cmd.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name");
        //    cmd.Parameters.Add("@Age", SqlDbType.Int, 3, "Age");

        //    dataAdapter = new SqlDataAdapter();
        //    dataAdapter.InsertCommand = cmd;
        //    try
        //    {
        //        conn.Open();
        //        dataAdapter.Update(dsSchool.StudentDT);
        //    }
        //    catch (Exception exp)
        //    {
        //        return false;
        //    }
        //    finally
        //    {
        //        conn.Close();
        //    }
        //    return true;
        //}
        //public School GetAllStudents2()
        //{
        //    cmd = new SqlCommand();
        //    cmd.Connection = conn;
        //    cmd.CommandText = "select * from student";
        //    dataAdapter = new SqlDataAdapter();
        //    dataAdapter.SelectCommand = cmd;
        //    //ds = new DataSet();
        //    School dsSchool = new School();
        //    try
        //    {
        //        conn.Open();
        //        dataAdapter.Fill(dsSchool.StudentDT);
        //    }
        //    finally
        //    {
        //        conn.Close();
        //    }
        //    return dsSchool;
        //}

        //public DataSet GetAllStudents()
        //{
        //    cmd = new SqlCommand();
        //    cmd.Connection = conn;
        //    cmd.CommandText = "select * from student";
        //    dataAdapter = new SqlDataAdapter();
        //    dataAdapter.SelectCommand = cmd;
        //    ds = new DataSet();
        //    try
        //    {
        //        conn.Open();
        //        dataAdapter.Fill(ds, "StudentLocal");
        //    }
        //    finally
        //    {
        //        conn.Close();
        //    }
        //    return ds;
        //}

        //public List<StudentInformation> GetStudents()
        //{
        //    cmd = new SqlCommand();
        //    cmd.Connection = conn;
        //    cmd.CommandText = "select * from student";
        //    List<StudentInformation> students = new List<StudentInformation>();
        //    try
        //    {
        //        conn.Open();
        //        SqlDataReader reader = cmd.ExecuteReader();
        //        StudentInformation student;
        //        while (reader.Read())
        //        {
        //            student = new StudentInformation();
        //            student.RollNo = Convert.ToInt64(reader["RollNo"]);
        //            student.Name = reader["Name"].ToString();
        //            student.Age = Convert.ToInt32(reader["Age"]);

        //            students.Add(student);
        //        }

        //    }
        //    catch (Exception exp)
        //    {
        //    }
        //    finally
        //    {
        //        conn.Close();
        //    }
        //    return students;
        //}
        //public bool InsertStudentData(StudentInformation student)
        //{
        //    cmd = new SqlCommand();
        //    cmd.Connection = conn;
        //    //cmd.CommandText = "insert into student values(111,'arun','A23')";
        //    cmd.CommandText = "insert into student values(" + student.RollNo + ",'" + student.Name + "'," + student.Age + ")";
        //    try
        //    {
        //        conn.Open(); // connect to DB
        //        cmd.ExecuteNonQuery(); //insert, update and delete
        //        //conn.Close(); // discconect from DB
        //        return true;
        //    }
        //    catch (Exception exp)
        //    {
        //        return false;
        //    }
        //    finally
        //    {
        //        conn.Close(); // discconect from DB
        //    }
        //}
        //public bool AddStudentData(StudentInformation student)
        //{
        //    cmd = new SqlCommand();
        //    cmd.Connection = conn;
        //    //cmd.CommandText = "insert into student(name,roll,age) values('arun',111,'A23')";  add(int n1, int n2)  / add(10,80)
        //    cmd.CommandText = "insert into student values(@RollNo,@Name,@Age)";
        //    cmd.Parameters.Add("@RollNo", SqlDbType.BigInt, 4);
        //    cmd.Parameters.Add("@Name", SqlDbType.VarChar, 100);
        //    cmd.Parameters.Add("@Age", SqlDbType.Int, 3);

        //    cmd.Parameters["@RollNo"].Value = student.RollNo;
        //    cmd.Parameters["@Name"].Value = student.Name;
        //    cmd.Parameters["@Age"].Value = student.Age;

        //    try
        //    {
        //        conn.Open(); // connect to DB
        //        cmd.ExecuteNonQuery(); //insert, update and delete
        //        //conn.Close(); // discconect from DB
        //        return true;
        //    }
        //    catch (Exception exp)
        //    {
        //        return false;
        //    }
        //    finally
        //    {
        //        conn.Close(); // discconect from DB
        //    }
        //}

    }
}
